package net.cz.wc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import net.cz.wc.data.ResourceManager;
import net.cz.wc.main.Context;
import net.cz.wc.olddto.IntervalDTO;
import net.cz.wc.olddto.OldLogDTO;
import net.cz.wc.olddto.OldMetricDTO;

public class SQLDao extends AbstractDAO {
	
	public SQLDao(Connection conn) {
		super(conn);
	}

	private static final String LOG_INSERT = 
			"INSERT INTO log(id, file_name, descr, load_time, stamp_type, file_type) VALUES (?, ?, ?, ?, ?, ?)";
	
	private static final String METRIC_INSERT = 
			"INSERT INTO metric(id_log, metric_time, metric_sequence, metric_name, metric_value, metric_start) VALUES (?, ?, ?, ?, ?, ?)";
	
	private static final String NEXTVAL_SEQ_LOG_ID = "select nextval ('seq_log_id')";
	
	/**
	 * Returns the next value for the log sequence
	 * @return
	 * @throws SQLException
	 */
	public long getNextLogID () throws SQLException {
		PreparedStatement pstm = null;
		ResultSet rst = null;
		long nextLog = -1;
		try {
			pstm = getConnection().prepareStatement(NEXTVAL_SEQ_LOG_ID);
			rst = pstm.executeQuery();
			
			rst.next();
			nextLog = rst.getLong(1);
		} finally {
			ResourceManager.closeResultSet(rst);
			ResourceManager.closePreparedStatement(pstm);
		}
		
		return nextLog;
	}
	
	/**
	 * Saves a log into the DB
	 * @param log
	 * @throws SQLException
	 */
	public void saveLog(OldLogDTO log) throws SQLException {
		PreparedStatement pstm = null;
		ResultSet rst = null;
		try {
			pstm = getConnection().prepareStatement(LOG_INSERT);
			pstm.setLong(1, log.getId());
			pstm.setString(2, log.getFileName());
			pstm.setString(3, log.getDescr());
			pstm.setDate(4, new java.sql.Date(log.getLoadTime().getTime()));
			pstm.setLong(5, log.getStampTime());
			pstm.setString(6, log.getFileType());
			
			pstm.executeUpdate();
		} finally {
			ResourceManager.closeResultSet(rst);
			ResourceManager.closePreparedStatement(pstm);
		}
	}
	
	/**
	 * Saves a metric into the DB
	 * @param oldMetric
	 * @throws SQLException
	 */
	public void saveMetric(OldMetricDTO oldMetric) throws SQLException {
		PreparedStatement pstm = null;
		ResultSet rst = null;
		
		try {
			pstm = getConnection().prepareStatement(METRIC_INSERT);
			pstm.setLong(1, oldMetric.getIdLog());
			pstm.setTimestamp(2, new java.sql.Timestamp(oldMetric.getMetricTime().getTime()));
			pstm.setLong(3, oldMetric.getMetricSequence());
			pstm.setString(4, oldMetric.getMetricName());
			pstm.setLong(5, oldMetric.getMetricValue());
			pstm.setTimestamp(6, new java.sql.Timestamp(oldMetric.getMetricStart().getTime()));
			
			pstm.executeUpdate();
		} finally {
			ResourceManager.closeResultSet(rst);
			ResourceManager.closePreparedStatement(pstm);
		}
	}
	
	/**
	 * Returns a list of Log objects in the database and filtered by filters option
	 * @param filters list to filter the log query
	 * @return list of OldLogDTO objects
	 * @throws SQLException
	 */
	public List<OldLogDTO> getLogs(List<String> filters) throws SQLException {
		
		List<OldLogDTO> resultSet = new ArrayList<OldLogDTO>();
		
		StringBuilder metricQuery = new StringBuilder("select id, file_name, load_time, file_type from log ");
		if (filters != null && filters.size() > 0) {
			metricQuery.append(" where");
			
			boolean notFirst = false;
			for (String filter : filters) {
				if (notFirst) {
					metricQuery.append(" and");
				}
				metricQuery.append(" ").append(filter);
				notFirst = true;
			}
		}
		
		//Order sentences could be a static attribute for any global dao
		metricQuery.append(" ").append("order by id asc");
		
		Context.getContext().addDebugMessage("{getLogs} executing query: " + metricQuery.toString());
		
		PreparedStatement logsPstm = null;
		ResultSet logsRst = null;
		
		try {
			logsPstm = getConnection().prepareStatement(metricQuery.toString());
			logsRst = logsPstm.executeQuery();
			
			while (logsRst.next()) {
				OldLogDTO logDto = new OldLogDTO();
				logDto.setId(logsRst.getLong(1));
				logDto.setFileName(logsRst.getString(2));
				logDto.setLoadTime(logsRst.getTimestamp(3));
				logDto.setFileType(logsRst.getString(4));
				
				resultSet.add(logDto);
			}
		} finally {
			ResourceManager.closeResultSet(logsRst);
			ResourceManager.closePreparedStatement(logsPstm);
		}
		
		return resultSet;
	}
	
	/**
	 * Returns the minimum and maximum date of the metric table.
	 * @return an List<Date> structure. Element 0 is the min date and 1 is the max date.
	 * @throws SQLException
	 */
	public List<Date> getMinAndMaxDate() throws SQLException {
		List<Date> resultSet = new ArrayList<Date>();
		
		StringBuilder metricQuery = new StringBuilder("select min(metric_time) as mindate, max(metric_time) as maxdate from metric");
		
		PreparedStatement logsPstm = null;
		ResultSet result = null;
		
		try {
			logsPstm = getConnection().prepareStatement(metricQuery.toString());
			result = logsPstm.executeQuery();
			
			while (result.next()) {
				resultSet.add(result.getTimestamp(1));
				resultSet.add(result.getTimestamp(2));
			}
		} finally {
			ResourceManager.closeResultSet(result);
			ResourceManager.closePreparedStatement(logsPstm);
		}
		
		return resultSet;
	}
	
	/**
	 * Returns the calculated information for a given date interval
	 * @param startDate
	 * @param endDate
	 * @return dto with the relevant information
	 * @throws SQLException
	 */
	public IntervalDTO getInterval(Date startDate, Date endDate, String metricName, String exclude) throws SQLException {
		String query = 
				"select avg(metric_value) as avgvalue, min(metric_value) " +
				"as minvalue, max(metric_value) as maxvalue, count(1) as metriccount " +
				"from metric where metric_time between ? and ? ";
		
		if (metricName != null) {
			query = query +	" and metric_name ~* '" + metricName + "'";
		}
		
		if (exclude != null) {
			query = query +	" and metric_name !~* '" + exclude + "'";
		}

		IntervalDTO dto = null;
		
		PreparedStatement interPstm = null;
		ResultSet rst = null;
		
		try {
			interPstm = getConnection().prepareStatement(query);
			interPstm.setTimestamp(1, new Timestamp(startDate.getTime()));
			interPstm.setTimestamp(2, new Timestamp(endDate.getTime()));
			rst = interPstm.executeQuery();
			
			if (rst.next()) {
				dto = new IntervalDTO();
				dto.setStart(startDate);
				dto.setEnd(endDate);
				dto.setValue(rst.getLong(1));
				dto.setMin(rst.getLong(2));
				dto.setMax(rst.getLong(3));
				dto.setCount(rst.getLong(4));
			}
		} finally {
			ResourceManager.closeResultSet(rst);
			ResourceManager.closePreparedStatement(interPstm);
		}
		
		return dto;
	}
	
	public List<Long> getValues(List<String> filters) throws SQLException {
		String query = "select metric_value from metric where metric_name ~* 'URL:.*:resptime'";
		PreparedStatement interPstm = null;
		ResultSet rst = null;
		
		List<Long> resultList = new ArrayList<Long>();
		
		try {
			interPstm = getConnection().prepareStatement(query);
			rst = interPstm.executeQuery();
			
			while (rst.next()) {
				resultList.add(rst.getLong(1));
			}
		} finally {
			ResourceManager.closeResultSet(rst);
			ResourceManager.closePreparedStatement(interPstm);
		}
	
		return resultList;
	}
}
